package database;

import java.util.ArrayList;

import XMLread.BuildingCSV;
import XMLread.Space;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This class is intended to help with access to the map database.
 * It provides methods for retrieving map data in various formats.
 */
public class MapDbAdapter {

	// building keys
	public static final String KEY_BUILDING_ID = "building_id";
	public static final String KEY_BUILDING_NAME = "building_name";
	public static final String KEY_SHORT_NAME = "short_name";
	public static final String KEY_SHAPE = "shape";

	// space keys
	public static final String KEY_SPACE_BUILDING_ID = "space_building_id";
	public static final String KEY_ROOM = "room";
	public static final String KEY_ROOM_LABEL = "room_label";
	public static final String KEY_FLOOR = "floor";
	
	private static final String TAG = "MapDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private static final int DATABASE_VERSION = 1;

	public static final String MAP_DATABASE_NAME = "mapdata";
	public static final String DATABASE_TABLE_BUILDING = "building";
	public static final String DATABASE_TABLE_SPACE = "space";

	/**
	 * Database creation sql statements
	 */
	private static final String CREATE_BUILDING_TABLE =
			"create table " + DATABASE_TABLE_BUILDING + " (" +
					KEY_BUILDING_NAME + " text primary key, " +	
					KEY_BUILDING_ID + " text not null, " +
					KEY_SHORT_NAME +" text not null, " +
					KEY_SHAPE + " shape text not null);";
	
	private static final String CREATE_SPACE_TABLE =
			"create table " + DATABASE_TABLE_SPACE + " (" +
				KEY_SPACE_BUILDING_ID + " text not null, " + 
				KEY_ROOM + " text not null, " +
				KEY_ROOM_LABEL + " text not null, " +
				KEY_FLOOR + " text not null," + 
				KEY_SHAPE + " text not null, " +
				"primary key(" + KEY_SPACE_BUILDING_ID + ", " + KEY_ROOM + "), "+
				"foreign key(" + KEY_SPACE_BUILDING_ID + ") references " + DATABASE_TABLE_BUILDING + 
					"(" + KEY_BUILDING_ID + ") );";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, MAP_DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_BUILDING_TABLE);
			db.execSQL(CREATE_SPACE_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_BUILDING);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_SPACE);
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx the Context within which to work
	 */
	public MapDbAdapter(Context context) {
		this.mCtx = context;
	}

	/**
	 * Open the course database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException if the database could be neither opened or created
	 */
	public MapDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}


	/**
	 * Adds a building to the database
	 * @param id	the id of the building
	 * @param name	the name of the building
	 * @param shortName 	a shortened name of the building
	 * @param shape		the shape of the building, in pairs of latitudes and longitudes
	 */
	public long addBuilding(String id, String name, String shortName, String shape) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_BUILDING_ID, id);
		initialValues.put(KEY_BUILDING_NAME, name);
		initialValues.put(KEY_SHORT_NAME, shortName);
		initialValues.put(KEY_SHAPE, shape);

		return mDb.insert(DATABASE_TABLE_BUILDING, null, initialValues);
	}
	
	
	/**
	 * Adds a space to the database
	 * @param id	the building id this room is associated with
	 * @param room	the room name
	 * @param roomLabel	the room label
	 * @param floor	the floor number
	 * @param shape the shape of this space, in pairs of latitudes and longitudes
	 */
	public long addSpace(String buildingId, String room, String roomLabel, String floor, String shape) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SPACE_BUILDING_ID, buildingId);
		initialValues.put(KEY_ROOM, room);
		initialValues.put(KEY_ROOM_LABEL, roomLabel);
		initialValues.put(KEY_FLOOR, floor);
		initialValues.put(KEY_SHAPE, shape);

		return mDb.insert(DATABASE_TABLE_SPACE, null, initialValues);
	}

	/**
	 * Delete a building with the given name
	 * 
	 * @param buildingName name of the building to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deleteBuilding(String buildingName) {
		return mDb.delete(DATABASE_TABLE_BUILDING, KEY_BUILDING_NAME + "=" + buildingName, null) > 0;
	}
	
	/**
	 * Delete a space from the database
	 * @param buildingId	the id of the building
	 * @param room			the room
	 * @return
	 */
	public boolean deleteSpace(String buildingId, String room) {
		return mDb.delete(DATABASE_TABLE_SPACE, KEY_SPACE_BUILDING_ID + "=" + buildingId + " AND " +
								KEY_ROOM + "=" + room, null) > 0;
	}

	/**
	 * Return a Cursor over the list of all buildings 
	 * @return Cursor over all buildings
	 */
	public Cursor fetchAllBuildings() {
		return mDb.query(DATABASE_TABLE_BUILDING, new String[] {KEY_BUILDING_NAME,
				KEY_BUILDING_ID, KEY_SHORT_NAME, KEY_SHAPE}, 
				null, null, null, null, null);
	}
	
	/**
	 * Return a Cursor over the list of all buildings 
	 * @return Cursor over all buildings
	 */
	public Cursor fetchAllSpaces() {
		return mDb.query(DATABASE_TABLE_SPACE, new String[] {KEY_BUILDING_ID, KEY_ROOM,
				KEY_ROOM_LABEL, KEY_FLOOR, KEY_SHAPE}, 
				null, null, null, null, null);
	}
	
	/**
	 * Return a Cursor over a list of spaces for a specific building
	 * @param buildingId
	 * @return
	 */
	public Cursor fetchSpacesForBuilding(String buildingId) {
		return mDb.query(DATABASE_TABLE_SPACE, null, 
				KEY_SPACE_BUILDING_ID + "=" + buildingId,
				null, null, null, null, null);
	}
	
	/**
	 * Gets a list of rooms for a given building
	 * @param buildingName	the name of the building
	 * @return	the list of rooms
	 */
	public ArrayList<String> getRooms(String buildingName) {
		String query = "SELECT " + KEY_ROOM + " FROM " + 
				DATABASE_TABLE_SPACE + " WHERE " +	KEY_SPACE_BUILDING_ID + " in (" +
				"SELECT " + KEY_BUILDING_ID + " FROM " + DATABASE_TABLE_BUILDING + 
				" WHERE " + KEY_BUILDING_NAME + " = '" + buildingName + "')";
		Cursor c = mDb.rawQuery(query, null);
		c.moveToFirst();
		
		int roomColumn = c.getColumnIndex(KEY_ROOM);
		
		ArrayList<String> rooms = new ArrayList<String>();
		
		while(!c.isAfterLast()) {
			rooms.add(c.getString(roomColumn));
			c.moveToNext();
		}
		
		return rooms;
	}
	
	/**
	 * Returns an array list of building names
	 * @param withClassrooms	whether or not buildings with classrooms will be returned
	 * @return	the array list of building names
	 */
	public ArrayList<String> getBuildingNames(boolean withClassrooms) {
		ArrayList<String> buildings = new ArrayList<String>();
		Cursor c;
		c = fetchAllBuildings();
		c.moveToFirst();
		
		int nameColumn = c.getColumnIndex(KEY_BUILDING_NAME);
		int idColumn = c.getColumnIndex(KEY_BUILDING_ID);
		
		while(!c.isAfterLast()){
			if(!withClassrooms)
				buildings.add(c.getString(nameColumn));
			else {
				if(!c.getString(idColumn).equals("-1"))
					buildings.add(c.getString(nameColumn));
			}
			c.moveToNext();
		}
		
		return buildings;
	}
	
	/**
	 * Gets a building object given its name
	 * @param buildingName	the name of the building
	 * @return	the building object
	 */
	public BuildingCSV getBuilding(String buildingName) {
		String query = "SELECT * " + 
						" FROM " + DATABASE_TABLE_BUILDING + 
						" WHERE " + KEY_BUILDING_NAME +
						" = '" + buildingName + "'";
		Cursor c = mDb.rawQuery(query, null);
		c.moveToFirst();
		int buildingColumn = c.getColumnIndex(KEY_BUILDING_NAME);
		int buildingIdColumn = c.getColumnIndex(KEY_BUILDING_ID);
		int shortNameColumn = c.getColumnIndex(KEY_SHORT_NAME);
		int shapeColumn = c.getColumnIndex(KEY_SHAPE);
		
		return new BuildingCSV(c.getString(shapeColumn), c.getString(buildingColumn),
						c.getString(shortNameColumn), c.getString(buildingIdColumn));		
	}
	
	/**
	 * Gets all spaces of a building given its name
	 * @param buildingID	the ID of the building
	 * @return	ArrayList of spaces that belong to the building
	 */
	public ArrayList<Space> getBuildingSpaces(int buildingID) {
		
		Cursor c = fetchSpacesForBuilding(Integer.toString(buildingID));
		c.moveToFirst();
		
		int spaceBuildingIDColumn = c.getColumnIndex(KEY_SPACE_BUILDING_ID);
		int roomColumn = c.getColumnIndex(KEY_ROOM);
		int roomLabelColumn = c.getColumnIndex(KEY_ROOM_LABEL);
		int floorColumn = c.getColumnIndex(KEY_FLOOR);
		int shapeColumn = c.getColumnIndex(KEY_SHAPE);
		
		ArrayList<Space> ourBuildingSpaces = new ArrayList<Space>();
		
		while(!c.isAfterLast()){
			ourBuildingSpaces.add(new Space(c.getString(shapeColumn), c.getString(spaceBuildingIDColumn),
					c.getString(floorColumn), c.getString(roomColumn), c.getString(roomLabelColumn)));
			c.moveToNext();
		}
		
		return ourBuildingSpaces;
	}
	
}
